package com.DefectTracking;

import com.DefectTracking.*;
import java.util.List;
import java.util.ArrayList;
import java.sql.*;



/*
      this class will interface with the Defect database
*/


public class DefectDetailManager {

      private String DB_DRIVER_NAME = "com.mysql.jdbc.Driver";
      private String DB_URL = "jdbc:mysql://127.0.0.1:3306/tomcatdb";
      private String DB_USER = "tomcat";
      private String DB_PASSWORD = "1111";
      
      public DefectDetailManager() {
          	//Load Driver
          	try {
          	    Class.forName(DB_DRIVER_NAME);
          	}catch(ClassNotFoundException e) {
          	    System.out.println("</br> can't find " + DB_DRIVER_NAME +" driver</br>");
          	    e.printStackTrace();	    
          	}//try-catch
  	
      }//public DefectManagerDetail()
              
      
      
      //retrieve defect detail
      public DefectDetail getDefectDetail(int defect_no){
          
          	DefectDetail defectDetail = new DefectDetail();            

  		Connection con = null; // default connection
  		Statement stmt = null;
  		ResultSet rs = null;    	
  		
	   	String sql_defect_detail = "select DEFECT.DefectID, DEFECT.Summary, DEFECT.Details, DEFECT.DetectedBy, DEFECT.DetectedOn, DEFECT.StatusID," + 
  		   	"DEFECT.ProjectID, DEFECT.ClosedDate, DEFECT.AssignedTo, USER.Fname, USER.Lname, DEFECTSTATUS.Description, " +
  		    	"(select Fname from USER where USER.UserID=DEFECT.AssignedTo) as a_fname, (select Lname from USER where USER.UserID=DEFECT.AssignedTo) as a_lname, " +
  		    	"(select email from USER where USER.UserID=DEFECT.AssignedTo) as a_emailadd " +
  		    	"from DEFECT, USER, DEFECTSTATUS " +
  		    	"where DEFECT.DefectID="+defect_no+" and USER.UserID = DEFECT.DetectedBy and DEFECT.StatusID = DEFECTSTATUS.StatusID " ;
  		    				
  		//retrieve defect detail from defect table, detector name from user table, defect status from defectstaus table
  		try{

  		    	//connect to database
  		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
  		    	//Create a Statement
  		    	stmt = con.createStatement();	    
  		    	//execute SQL
  		    	rs = stmt.executeQuery(sql_defect_detail);
  	    
  		    	while(rs.next()) {
   				
  				defectDetail.setDefectID(rs.getInt(1));
  				defectDetail.setSummary(rs.getString(2));
  				defectDetail.setDetails(rs.getString(3));
  				defectDetail.setDetectedBy(rs.getInt(4));
  				defectDetail.setDetectedOn(rs.getDate(5));
  				defectDetail.setStatusID(rs.getInt(6));
  				defectDetail.setProjectID(rs.getInt(7));
  				defectDetail.setClosedDate(rs.getDate(8));
  				defectDetail.setAssignedTo(rs.getInt(9));
  				defectDetail.setDetectedByFname(rs.getString(10));
  				defectDetail.setDetectedByLname(rs.getString(11));
  				defectDetail.setDefectstatusName(rs.getString(12));
  				defectDetail.setAssigneeFname(rs.getString(13));
  				defectDetail.setAssigneeLname(rs.getString(14)); 
  				defectDetail.setEmailAdd(rs.getString(15));

  		    	}//while(rx.next())	  

  		} catch (SQLException e){
  		    	System.out.println( "Error Source : DefectDetailManager.java : SQLException" );
  		    	System.out.println( "SQL_state : " + sql_defect_detail );
  		    	System.out.println( "SQLState : " + e.getSQLState() );
  		    	System.out.println( "Message : " + e.getMessage() );
  		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );
	   

  		} finally {
  		    	try {
  		    	    if ( rs != null ) rs.close();
  		    	    if ( stmt != null) stmt.close();
  		    	    if ( con != null) con.close();
  		    	} catch ( SQLException e ){
				
  		    	}//try-catch    
  		}//try-catch-finally
  		   	
          
          return defectDetail;
          
      }// public Defect getDefectDetail()


      

      
      //update defect detail
      public int setDefectDetailUpdate(String sql_defectdetail_update){

  		Connection con = null; // default connection
  		Statement stmt = null;
  		int result_update = 0; //if update is success then return result_update > 0
  		
		//update defect_detail_update status(details, assignee, defectstatus, closedate(if possible))
  		try{

  		    	//connect to database
  		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
  		    	//Create a Statement
  		    	stmt = con.createStatement();	    
  		    	//execute SQL
  		    	result_update = stmt.executeUpdate(sql_defectdetail_update);  	    
  		    	
  		} catch (SQLException e){
  		    	System.out.println( "Error Source : DefectDetailManager.java : SQLException" );
  		    	System.out.println( "SQL_state : " + sql_defectdetail_update );
  		    	System.out.println( "SQLState : " + e.getSQLState() );
  		    	System.out.println( "Message : " + e.getMessage() );
  		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );
	   

  		} finally {
  		    	try {
  		    	    if ( stmt != null) stmt.close();
  		    	    if ( con != null) con.close();
  		    	} catch ( SQLException e ){
				
  		    	}//try-catch    
  		}//try-catch-finally
  		   	
          
          return result_update;
          
      }// public setDefectDetailUpdate(String sql_defectdetail_update)

      
      
      
      //retrieve defect detail
      public int setDefectInsert(String sql_defectdetails_insert){

  		Connection con = null; // default connection
  		Statement stmt = null;
  		int result_insert = 0; //if insert is success then return result_insert > 0  /////need to check
  		
		//insert new defect_details 
  		try{

  		    	//connect to database
  		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
  		    	//Create a Statement
  		    	stmt = con.createStatement();	    
  		    	//execute SQL
  		    	result_insert = stmt.executeUpdate(sql_defectdetails_insert);  	    
  		    	
  		} catch (SQLException e){
  		    	System.out.println( "Error Source : DefectDetailManager.java : SQLException" );
  		    	System.out.println( "SQL_state : " + sql_defectdetails_insert );
  		    	System.out.println( "SQLState : " + e.getSQLState() );
  		    	System.out.println( "Message : " + e.getMessage() );
  		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );
	   

  		} finally {
  		    	try {
  		    	    if ( stmt != null) stmt.close();
  		    	    if ( con != null) con.close();
  		    	} catch ( SQLException e ){
				
  		    	}//try-catch    
  		}//try-catch-finally
  		   	
          
          return result_insert;
          
      }// public Defect setDefectInsert()

      
      
      
      
      //retrieve defect status information
      public List<DefectStatus> getDefectStatusInfo(){
          
	  	List<DefectStatus> defectStatusTable = new ArrayList<DefectStatus>();            

    		Connection con = null; // default connection
		Statement stmt = null;
		ResultSet rs = null;    	
	
		String sql_defectstatus = "select StatusID, Description from DEFECTSTATUS" ;
		
		//retrieve defect detail from defect table, detector name from user table, defect status from defectstaus table
		try{
		   
		    	//connect to database
		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
		    	//Create a Statement
		    	stmt = con.createStatement();	    
		    	//execute SQL
		    	rs = stmt.executeQuery(sql_defectstatus);
	    
		    	while(rs.next()) {
		    	    
		    	    	DefectStatus defectStatus = new DefectStatus(rs.getInt(1), rs.getString(2)); 		    	    	
				//defectStatus.setStatusID(rs.getInt(1));
				//defectStatus.setDescription(rs.getString(2)); 
				defectStatusTable.add(defectStatus);
		    	
		    	}//while(rx.next())	  

		} catch (SQLException e){
		    	System.out.println( "Error Source: DefectDetailManager.java : SQLException" );
		    	System.out.println( "SQL_state : " + sql_defectstatus );
		    	System.out.println( "SQLState : " + e.getSQLState() );
		    	System.out.println( "Message : " + e.getMessage() );
		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );	   

		} finally {
		    	try {
		    	    if ( rs != null ) rs.close();
		    	    if ( stmt != null) stmt.close();
		    	    if ( con != null) con.close();
		    	} catch ( SQLException e ){
				
		    	}//try-catch    
		}//try-catch-finally		   	
    
		return defectStatusTable;
    
      }//public ArrayList<DefectStatus> getDefectStatusInfo() 
      
      
      
      
      //retrieve defect status
      public List<User> getAdministratorInfo(){
          
	  	List<User> adminTable = new ArrayList<User>();            

    		Connection con = null; // default connection
		Statement stmt = null;
		ResultSet rs = null;    	
		
		String sql_useradmins = "select UserID, FName, LName from USER where AccessLevelID=1" ; //AccessLevelID=1 is administrator
		
		//retrieve defect detail from defect table, detector name from user table, defect status from defectstaus table
		try{
		    			    			    				
		    	//connect to database
		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
		    	//Create a Statement
		    	stmt = con.createStatement();	    
		    	//execute SQL
		    	rs = stmt.executeQuery(sql_useradmins);
	    
		    	while(rs.next()) {
		    	    
		    	    	User useradmins = new User();
		    	    	useradmins.setUserID(rs.getInt(1));
		    	    	useradmins.setFName(rs.getString(2));
		    	    	useradmins.setLName(rs.getString(3));			 
		    	    	adminTable.add(useradmins);
		    	
		    	}//while(rx.next())	  

		} catch (SQLException e){
		    	System.out.println( "Error Source:Welcome.jsp : SQLException" );
		    	System.out.println( "SQL_state : " + sql_useradmins );
		    	System.out.println( "SQLState : " + e.getSQLState() );
		    	System.out.println( "Message : " + e.getMessage() );
		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );	   

		} finally {
		    	try {
		    	    if ( rs != null ) rs.close();
		    	    if ( stmt != null) stmt.close();
		    	    if ( con != null) con.close();
		    	} catch ( SQLException e ){
				
		    	}//try-catch    
		}//try-catch-finally
		   	
    
		return adminTable;
    
      }//public ArrayList<DefectStatus> getDefectStatusInfo() 
      
      
      
      
      
     //retrieve user information
      public int getUserAccessLevelID(int user_id){
          
    		int curr_accessLevelID = 0;           

    		Connection con = null; // default connection
		Statement stmt = null;
		ResultSet rs = null;    	
	
		//retrieve defect detail from defect table, detector name from user table, defect status from defectstaus table
		try{
		    
		    	String sql_state_userinfo = "select AccessLevelID from USER where UserID="+user_id ;
		    				
		    	//connect to database
		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
		    	//Create a Statement
		    	stmt = con.createStatement();	    
		    	//execute SQL
		    	rs = stmt.executeQuery(sql_state_userinfo);
	    
		    	while(rs.next()) {
				
		    	    	curr_accessLevelID = rs.getInt(1);
		    	
		    	}//while(rx.next())	  

		} catch (SQLException e){
		    	System.out.println( "Error Source:Welcome.jsp : SQLException" );
		    	System.out.println( "SQLState : " + e.getSQLState() );
		    	System.out.println( "Message : " + e.getMessage() );
		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );
	   

		} finally {
		    	try {
		    	    if ( rs != null ) rs.close();
		    	    if ( stmt != null) stmt.close();
		    	    if ( con != null) con.close();
		    	} catch ( SQLException e ){
				
		    	}//try-catch    
		}//try-catch-finally
		   	
		return curr_accessLevelID;
    
      }//DefectStatus getDefectStatus() 
      
      
      
    //retrieve user emailadd
      public String getUserEmailAdd(int user_id){
          
    		Connection con = null; // default connection
		Statement stmt = null;
		ResultSet rs = null;  
		
		String EmailAdd = null;
	
		//retrieve defect detail from defect table, detector name from user table, defect status from defectstaus table
		try{
		    
		    	String sql_state_userinfo = "select Email from USER where UserID="+user_id ;
		    				
		    	//connect to database
		    	con = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);	    
		    	//Create a Statement
		    	stmt = con.createStatement();	    
		    	//execute SQL
		    	rs = stmt.executeQuery(sql_state_userinfo);
	    
		    	while(rs.next()) {		
		    	    
		    	    	EmailAdd = rs.getString(1);
		    	
		    	}//while(rx.next())	  

		} catch (SQLException e){
		    	System.out.println( "Error Source:Welcome.jsp : SQLException" );
		    	System.out.println( "SQLState : " + e.getSQLState() );
		    	System.out.println( "Message : " + e.getMessage() );
		    	System.out.println( "Mysql Error Code : " + e.getErrorCode() );
	   

		} finally {
		    	try {
		    	    if ( rs != null ) rs.close();
		    	    if ( stmt != null) stmt.close();
		    	    if ( con != null) con.close();
		    	} catch ( SQLException e ){
				
		    	}//try-catch    
		}//try-catch-finally
		   	
		return EmailAdd;
    
      }//public getUserEmailAdd()
      
      
}//class